package cn.gson.crm.model.dao;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import cn.gson.crm.model.Jdbc;
import cn.gson.crm.model.entity.Dept;
import cn.gson.crm.model.entity.Position;

/**
 * 部门DAO层
 * 
 * @author Mr.L
 *
 */
public class DeptDao {
	// 页大小
	private int pageSize = 7;

	/**
	 * 查询所有信息
	 * 
	 * @return
	 * @throws SQLException
	 */
	public List<Dept> getAllDept() throws SQLException {
		String sql = "select dept_id,dept_name,dept_manager,updept_id from dept where dept_status = 1 order by dept_id desc";
		List<Map<String, Object>> map = Jdbc.getInstace().executeQueryForList(sql);
		List<Dept> dept = new ArrayList<>();
		for (Map<String, Object> mm : map) {
			Dept d = new Dept();
			d.setDept_id(mm.get("dept_id").toString());

			d.setDept_name((String)mm.get("dept_name"));
			d.setEmp_manager(getDeptManagerByEmpId(Integer.parseInt(mm.get("dept_id").toString())));
			d.setDept_manager(Integer.parseInt(mm.get("dept_manager").toString()));
			d.setDept_name((String) mm.get("dept_name"));
			d.setDept_manager(Integer.parseInt(mm.get("dept_manager").toString()));

			d.setUpdept_name(getUpDeptNameByDeptId(Integer.parseInt(mm.get("dept_id").toString())));
			d.setUpdapt_id(Integer.parseInt(mm.get("updept_id").toString()));

			dept.add(d);
		}
		return dept;
	}

	/**
	 * 查询所有部门名称和ID
	 * 
	 * @return
	 * @throws SQLException
	 */
	public List<Dept> getAllDeptNameAndId() throws SQLException {
		String sql = "Select dept_id,dept_name from Dept where dept_status = 1";
		List<Map<String, Object>> map = Jdbc.getInstace().executeQueryForList(sql);
		List<Dept> dept = new ArrayList<>();
		for (Map<String, Object> mm : map) {
			Dept d = new Dept();
			d.setDept_id(mm.get("dept_id").toString());
			d.setDept_name((String) mm.get("dept_name"));

			dept.add(d);
		}
		return dept;
	}
	
	/**
	 * 根据总部门的id值查询以此为上属部门的部门
	 * @param id
	 * @return
	 * @throws Exception
	 */
	public List<Dept> getSonDeptByUpdateId(String id) throws Exception {
		String sql = "select * from dept where updept_id = ? and dept_status = 1";
		List<Map<String, Object>> data = Jdbc.getInstace().executeQueryForList(sql, id);
		Dept dept = null;
		List<Dept> deptList = new ArrayList<>();
		for (Map<String, Object> map : data) {
			dept = bindDept(map);
			deptList.add(dept);
		}
		return deptList;

	}

	/**
	 * 根据ID查询一条信息
	 * 
	 * @param id
	 * @return
	 * @throws SQLException
	 */
	public Dept getOneByDeptId(int id) throws Exception {
		String sql = "select dept_id,dept_name,dept_manager,updept_id from dept where dept_status = 1 and dept_id = ?";
		Map<String, Object> map = Jdbc.getInstace().executeQuery(sql, id);
		Dept d = bindDept(map);
		return d;
	}

	/**
	 * 查询上属部门名称
	 * 
	 * @param id
	 * @return
	 * @throws SQLException
	 */
	public String getUpDeptNameByDeptId(int id) throws SQLException {
		String sql = "select dept_name from dept where dept_id = (select updept_id from dept where dept_id = ?)";
		List<Map<String, Object>> map = Jdbc.getInstace().executeQueryForList(sql, id);
		String name = null;
		for (Map<String, Object> mm : map) {
			name = (String) mm.get("dept_name");
		}
		return name;
	}

	/**
	 * 查询部门经理名称
	 * @param empId
	 * @return
	 * @throws SQLException
	 */
	public String getDeptManagerByEmpId(int empId) throws SQLException{
		String sql = "select emp_name from employee where emp_id = (select dept_manager from dept where dept_id = ?)";
		List<Map<String,Object>> map = Jdbc.getInstace().executeQueryForList(sql,empId);
		String deptManager = null;
		for (Map<String,Object> mm : map) {
			deptManager = (String)mm.get("emp_name");
		}
		return deptManager;
	}
	
	/**
	 * 新增或修改部门
	 * 
	 * @param dept
	 * @return
	 * @throws SQLException
	 */
	public boolean insertDept(Dept dept) throws SQLException {
		String sql = null;
		// 根据ID判断是新增还是修改
		if (dept.getDept_id() == null) {
			sql = "insert dept(dept_name,dept_manager,dept_status,updept_id) values(?,?,?,?)";
			return Jdbc.getInstace().executeUpdate(sql, dept.getDept_name(), dept.getDept_manager(), 1,
					dept.getUpdapt_id());
		} else {
			sql = "update dept set dept_name = ?,dept_manager = ?,updept_id = ? where dept_id = ?";
			return Jdbc.getInstace().executeUpdate(sql, dept.getDept_name(), dept.getDept_manager(),
					dept.getUpdapt_id(), dept.getDept_id());
		}
	}

	/**
	 * 根据部门名称判断是否已存在
	 * 
	 * @param name
	 * @return
	 * @throws SQLException
	 */
	public Long searchDeptName(String name) throws Exception {
		String sql = "select count(*) name from dept where dept_name = ?";
		Map<String, Object> map = Jdbc.getInstace().executeQuery(sql, name);
		Long name1 = (Long) map.get("name");
		return name1;
	}

	/**
	 * 根据部门名称判断是否已存在,同时查看他的状态
	 * 
	 * @param name
	 * @return
	 * @throws SQLException
	 */
	public Dept searchDeptNameAndSearchStatus(String name) throws Exception {
		String sql = "select dept_status,dept_id from dept where dept_name = ?";
		Map<String, Object> map = Jdbc.getInstace().executeQuery(sql, name);
		Dept dept = new Dept();
		dept.setDept_status(Integer.parseInt(map.get("dept_status").toString()));
		dept.setDept_id(map.get("dept_id").toString());
		return dept;
	}

	/**
	 * 判断需要删除的部门下面是否有子部门
	 * 
	 * @param updeptId
	 * @return
	 * @throws Exception
	 */
	public Long searchUpDeptId(int updeptId) throws Exception {
		String sql = "select COUNT(*) count from dept where updept_id = ? and dept_status = 1";
		Map<String, Object> map = Jdbc.getInstace().executeQuery(sql, updeptId);
		Long count = (Long) map.get("count");
		return count;
	}

	/**
	 * 删除即时修改状态
	 * 
	 * @param id
	 * @return
	 * @throws SQLException
	 */
	public boolean deleteDept(int id, int status, int updeptId, String manager) throws SQLException {
		String sql = null;
		if (status > 0) {
			sql = "update dept set dept_status = 1,updept_id = ?,dept_manager = ? where dept_id = ?";
			return Jdbc.getInstace().executeUpdate(sql, updeptId, manager, id);
		}
		sql = "update dept set dept_status = 0 where dept_id = ?";
		return Jdbc.getInstace().executeUpdate(sql, id);
	}

	/**
	 * 查询分页数据
	 * 
	 * @param type
	 * @param page
	 * @return
	 * @throws Exception
	 */
	public List<Dept> getPageDeptList(int page) throws Exception {
		int start = (page - 1) * pageSize;
		String sql = "select * from dept where dept_status = 1 order by dept_id desc limit ?,?";
		return queryDeptList(sql, start, pageSize);
	}

	/**
	 * 获取总页数
	 * 
	 * @param type
	 * @return
	 * @throws SQLException
	 */
	public int getTotalPage() throws SQLException {
		String sql = "select count(*) total from dept where dept_status = 1";
		Map<String, Object> map = Jdbc.getInstace().executeQuery(sql);
		Integer total = Integer.parseInt(map.get("total").toString());
		return (int) Math.ceil(total.doubleValue() / pageSize);
	}

	private List<Dept> queryDeptList(String sql, Object... params) throws Exception {
		List<Map<String, Object>> listData = Jdbc.getInstace().executeQueryForList(sql, params);
		List<Dept> deptList = null;
		if (listData != null && listData.size() > 0) {
			deptList = new ArrayList<>();
			for (Map<String, Object> data : listData) {
				deptList.add(bindDept(data));
			}
		}
		return deptList;
	}

	/**
	 * 实体类封装
	 * 
	 * @param data
	 * @return
	 * @throws NumberFormatException
	 * @throws SQLException
	 */
	private Dept bindDept(Map<String, Object> data) throws Exception {
		Dept d = new Dept();
		d.setDept_id((data.get("dept_id").toString()));

		d.setDept_name((String)data.get("dept_name"));
		d.setDept_manager(Integer.parseInt(data.get("dept_manager").toString()));

		d.setUpdapt_id(Integer.parseInt(data.get("updept_id").toString()));
		d.setEmp_manager(getDeptManagerByEmpId(Integer.parseInt(data.get("dept_id").toString())));
		d.setUpdept_name(getUpDeptNameByDeptId(Integer.parseInt(data.get("dept_id").toString())));
		return d;
	}
}
